package com.scalar.chiptrack.commons.bean;

import com.scalar.chiptrack.commons.forms.ResultFormBean;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;

import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

/**
 * User: Ganesh
 * Date: Apr 29, 2008
 * Time: 11:19:29 AM
 */
public class IPaginatorImpl implements IPaginator{
    protected String identificationQuery;
    protected String retrievalQuery;
    protected String sortColumn;

    protected DBTransaction dbTransaction;
    protected List idList = null;

    protected boolean ascendingSortOrder;

    protected int paginationSize;
    protected int totalRecords;
    protected int totalPages;
    protected int currentPage = 1;
    protected int startIndex;
    protected int lastIndex;

    protected HashMap retQp;
    protected HashMap idQp;

    private static final String ID_LIST_PARAMETER = "ID_LIST";
    private static final String ORDER_BY_CLAUSE = "ORDER BY";
    private static final String ASCENDING_SORT_ORDER = "ASC";
    private static final String DESCENDING_SORT_ORDER = "DESC";

    public IPaginatorImpl(){

    }

    public IPaginatorImpl(String identificationQuery, HashMap idQp, String retrievalQuery, HashMap retQp, int paginationSize, String sortColumn, boolean ascendingSortOrder, DBTransaction dbTransaction )
    {
        this();
        this.identificationQuery = identificationQuery;
        this.idQp = idQp;
        this.retrievalQuery = retrievalQuery;
        this.retQp = retQp;
        this.paginationSize = paginationSize;
        this.sortColumn = sortColumn;
        this.ascendingSortOrder = ascendingSortOrder;
        this.dbTransaction = dbTransaction;

        try
		{
            invokeIdentificationQuery();
        }
        catch(Exception e)
		{
            System.out.println("exception in IPagnatorImpl constructor");
            e.printStackTrace();
        }
    }

    public IResultBean getPage(ResultFormBean searchFormBean) throws Exception 
	{
        // if the search order has changed, we need to do the identification
        // query again, reset the sort column and execute the query
        IResultBean result = null;
        try
		{
            sortColumn = searchFormBean.getSortColumn();
            String sortOrder = searchFormBean.getSortOrder().trim();

            if ( sortOrder.equalsIgnoreCase( ASCENDING_SORT_ORDER ) )
                ascendingSortOrder = true;
            else
                ascendingSortOrder = false;

            invokeIdentificationQuery();
            result = getPage( searchFormBean.getSearchPage() );
        }
        catch( Exception e )
        {
            e.printStackTrace();
            System.out.println( "Exception in paginatorImpl getpage()" );
        }
        return result;
    }

	public IResultBean getPage( int pageNumber ) throws Exception 
	{
	    IResultBean result 	= null;
	    Connection conn 	= null;
	    PreparedStatement pstmt = null;
	    ResultSet rs 		= null;

	    try
		{
	    	if ( ( idList != null ) && ( idList.size() > 0 ) )
            {
	    		currentPage = pageNumber;

                startIndex = ( currentPage - 1 ) * paginationSize;
                lastIndex  = ( startIndex + paginationSize );
                
                if( lastIndex > totalRecords )
                    lastIndex = totalRecords;
                
                List subList = idList.subList( startIndex, lastIndex );
                
                if( ( subList != null ) && ( subList.size() > 0 ) )
                {
                    String retrievalQuery = buildIdQuery( subList, this.retrievalQuery );
                    populateParameters( subList );
                    StringBuffer buffer = new StringBuffer( retrievalQuery );
                    appendSortClause( buffer );
                    //rs = (executeQuery(buffer.toString(),retQp));
                    if( dbTransaction != null )
                    	conn = dbTransaction.getConnection();
                    else
                        conn = DataSource.getConnection();
                    
                    pstmt = conn.prepareStatement( buffer.toString() );

                    if( retQp != null )
                    {
                        Iterator keyIterator = retQp.keySet().iterator();
                        while( keyIterator.hasNext() )
                        {   
                        	String key = (String)keyIterator.next();
                            String paramValue = (String)retQp.get( key );
                            int keyInt = Integer.parseInt( key );
                            pstmt.setString( keyInt, paramValue );
                        }
                    }
                    
                    rs = pstmt.executeQuery();
                    result = new IResultBeanImpl(rs, paginationSize, totalPages, currentPage, sortColumn, ascendingSortOrder );
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
            System.out.println("Exception in paginatorimpl getPage");
        }
        finally
		{
        	try
			{
        		if( rs != null ){ rs.close(); }
        		if( pstmt != null ){ pstmt.close(); }
        		if( conn != null ){ conn.close(); }
        	}
        	catch(Exception e)
			{
        		
			}
		}
        return result;
    }
    /**
     * Get total number of pages in the paginator depending on
     * pagination size
     * @return noOfPages int
     */
    public int getNumberOfPages() {
        return totalPages;
    }
    /**
     * Get total number of records returned
     * @return int
     */
    public int getNumberOfRecords() {
        return totalRecords;
    }
     public int getStartIndex() {
        return startIndex+1;
    }

    public int getLastIndex() {
        return lastIndex;
    }


    public IResultBean getResults(DBTransaction dbTransaction) throws Exception 
	{
        IResultBean result = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try
		{
            if( ( idList != null ) && ( idList.size() > 0 ) )
            {
                int startIndex, endIndex;
                startIndex = 0;
                endIndex = totalRecords;
                List subList = idList.subList( startIndex, endIndex );
                
                if( ( subList != null ) && ( subList.size() > 0 ) )
                {
                    //replacing the list with questionmarks
                    String retrievalQuery = buildIdQuery( subList, this.retrievalQuery );
                    //putting the parameters in hashmap
                    populateParameters( subList );
                    // append order by sort clause for retrieval as ordering
                    // is not guaranteed by the IN CLAUSE
                    StringBuffer buffer = new StringBuffer( retrievalQuery );
                    appendSortClause( buffer );

                    //rs = executeQuery(buffer.toString(),retQp);
                    if( dbTransaction != null )
                    	conn = dbTransaction.getConnection();
                    else
                        conn = DataSource.getConnection();
                    
                    pstmt = conn.prepareStatement( buffer.toString() );

                    if( retQp != null )
                    {
                        Iterator keyIterator = retQp.keySet().iterator();
                        while( keyIterator.hasNext() )
                        {   
                        	String key = (String)keyIterator.next();
                            String paramValue = (String)retQp.get( key );
                            int keyInt = Integer.parseInt( key );
                            pstmt.setString( keyInt, paramValue );
                        }
                    }
                    rs = pstmt.executeQuery();
                    result = new IResultBeanImpl(rs, paginationSize, totalPages, currentPage, sortColumn, ascendingSortOrder);
                }
            }
        }
        catch(Exception e){
            e.printStackTrace();
            System.out.print("Exception in paginatorImpl getPage");
        }
        finally
		{
        	try
			{
        		if( rs != null ){ rs.close(); }
        		if( pstmt != null ){ pstmt.close(); }
        		if( conn != null ){ conn.close(); }
        	}
        	catch(Exception e)
			{
        		
			}
		}
        return result;
    }
    //String identificationQuery, HashMap idQp, String retrievalQuery, HashMap retQp, int paginationSize, String sortColumn, boolean ascendingSortOrder, DBTransaction dbTransaction
    public void invokeIdentificationQuery()
    {
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try
		{
            StringBuffer sb = new StringBuffer(identificationQuery);

            appendSortClause( sb );
            //rs = executeQuery( sb.toString(), idQp );
            
            if( dbTransaction != null )
            	conn = dbTransaction.getConnection();
            else
                conn = DataSource.getConnection();
            
            pstmt = conn.prepareStatement( sb.toString() );

            if(idQp != null)
            {
                Iterator keyIterator = idQp.keySet().iterator();
                while(keyIterator.hasNext())
                {   String key = (String)keyIterator.next();
                    String paramValue = (String)idQp.get(key);
                    int keyInt = Integer.parseInt(key);
                    pstmt.setString(keyInt,paramValue);
                }
            }

            rs = pstmt.executeQuery();

            idList = new ArrayList();
            if( rs != null )
            {
                while( rs.next() )
                {
                    String id = rs.getString(1);
                    idList.add( id );
                }
            }
        }
        catch(Exception e)
        {
            System.out.println("Exception in invokeIdentification Query");
            e.printStackTrace();
        }
        finally
		{
        	try
			{
        		if( rs != null ){ rs.close(); }
        		if( pstmt != null ){ pstmt.close(); }
        		if( conn != null ){ conn.close(); }
        	}
        	catch(Exception e)
			{
        		
			}
        	
		}
        totalRecords = idList.size();
        totalPages = (int) Math.ceil((double)totalRecords / paginationSize);
    }

    /**
     * Method to build the query with question marks..
     * it prepares the query with ?
     *
     * @return String
     */
    public String buildIdQuery( List subList, String query )
    {
        StringBuffer buffer  = null;
        if( subList != null && subList.size() > 0 )
        {
            for( int i = 0; i < subList.size(); i++ )
            {
                if( buffer == null )
                {
                    buffer = new StringBuffer();
                    buffer.append( "?" );
                }
                else
                    buffer.append( ",?" );
            }
        }

        if( buffer != null )
        {
            String replacedString = buffer.toString();
            query = query.replaceFirst("ID_LIST",replacedString);
        }
        return query;
    }


/*    public ResultSet executeQuery(String query, HashMap queryParams){
        //System.out.println("IPaginatorImpl.executeQuery");
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            if( dbTransaction != null )
                conn = dbTransaction.getConnection();
            else
                conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( query );

            if(queryParams != null)
            {
                Iterator keyIterator = queryParams.keySet().iterator();
                while(keyIterator.hasNext())
                {   String key = (String)keyIterator.next();
                    String paramValue = (String)queryParams.get(key);
                    int keyInt = Integer.parseInt(key);
                    pstmt.setString(keyInt,paramValue);
                }
            }

            rs = pstmt.executeQuery();
        }
        catch(Exception e)
        {
            System.out.println("Exception in IpagnatorImpl execute Query");
            e.printStackTrace();
        }
        return rs;
    }*/
    /**
     * Appends the ORDER BY clause to a query, if sort column is provided
     * @param query StringBuffer
     */
    private void appendSortClause(StringBuffer query)
    {
        if (sortColumn != null)
        {
            query.append(" ").append(ORDER_BY_CLAUSE).append(" ");
            query.append(sortColumn);
            if (ascendingSortOrder)
                query.append(" ").append(ASCENDING_SORT_ORDER);
            else
                query.append(" ").append(DESCENDING_SORT_ORDER);
        }
    }
    /**
     * Method to populate the ids in query
     *
     */
     public void populateParameters(List subList)
     {
         retQp = new HashMap();
         if(subList!=null && subList.size()>0)
         {
             for(int i=0;i<subList.size();i++)
             {
                 retQp.put(""+(i+1),subList.get(i));
             }
         }
     }
}
